IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetContractDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetContractDetails]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/************************************************************************************************/          
/* Name  :GetContractDetails                                                               */          
/* Purpose :Procedure for getting the contract details            */          
/* PARAMETERS          
* Name       Description               
* -------------   -------------------------------------------          
*  @domainUserId Domain User ID          
*  @agreementId  Agreement ID          
*  @fundNumber   fundnumber for the agreement        
* ERRORS USED   Description          
* -------------   -------------------------------------------          
* n/a          
*          
* RESULT SETs          
---ContractDetails          
          
* RETURN VALUE          
* Value       Description               
* -------------   -----------------------------------------------          
* n/a          
*          
******************************************************************/           
--------------------------------------------------------------------
--Modifications:
--09-04-2007	Performance improvements: introduce (nolock)
--------------------------------------------------------------------
CREATE PROCEDURE [dbo].[GetContractDetails]          
(           
 @domainUserId INTEGER,          
 @agreementId INTEGER,        
 @fundNumber varchar(30)= Null          
)          
AS          
BEGIN          
SET NOCOUNT ON          
 DECLARE @awardedBudget INTEGER           
 DECLARE @budgetTotalLine INTEGER      
DECLARE @fundid INTEGER    
 SET @awardedBudget = 3 --Awarded budget          
 SET @budgetTotalLine = 15       
select  @fundid = id from agrfund where fundnumber = @fundnumber   
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>          
-- G E T  Sub Contractor   R E S U L T S E T  F O R  U S E R            
-- Return a complete Sub Contractor information resultset from the agreeementId          
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>          
          
select SubcontractorName,TotalBudget, AwardedToDate from AgrAgreementBudgetSubcontractor where FundNumber = @fundNumber

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>          
-- G E T  Related Protocols    R E S U L T S E T  F O R  U S E R            
-- Return a complete Related Protocols  information resultset from the agreementId          
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>          
SELECT             
  pro.Title            
 ,pro.ShortTitle AS 'Name'            
 ,pro.ProtocolNumber AS 'Number'            
 , ISNULL(agrfct.PILastName,'') + ', ' + ISNULL(agrfct.PIFirstName,'') AS 'PIName'            
 ,CASE WHEN LEN(pro.SubStatusId) > 0 THEN            
   status.[Name] + ', ' + substatus.[Name]             
   ELSE status.[Name]             
  END AS 'OverallStatus'            
--Protocol types are not yet available and currently            
--,there is only have 1 protocol type which is "Human".            
, case pro.ModuleType     
 When 'A' then 'Animal'  
 else 'Human'  
 end       as Type    
 , pro.ExpirationDate    
,case pro.ModuleType     
 When 'A' then NumberOfAnimals   
 else Approved  
 end   
 as Approved  
,Enrolled          
FROM             
 Agreement_Protocol_Relate agrProtocol (nolock)            
INNER JOIN            
 AgrAgreement_fct agrfct (nolock)         
ON            
 agrfct.FolderNumber = agrProtocol.AgreementFolderNumber            
INNER JOIN            
 Protocol pro (nolock)            
ON            
 pro.ProtocolNumber = agrProtocol.ProtocolNumber            
INNER JOIN             
 ProtocolOverallStatusType status (nolock)            
ON            
 pro.overallstatusid = status.[id]            
LEFT OUTER JOIN            
 ProtocolSubStatusType substatus (nolock)            
ON            
 pro.SubStatusId = substatus.[id]            
WHERE             
  agrfct.[ID] =  (SELECT MAX(af1.[ID]) FROM AgrAgreement_fct af1 (nolock)            
                           WHERE af1.FolderNumber  = agrfct.FolderNumber)            
AND             
 pro.DocumentNumber  = (SELECT MAX(p1.DocumentNumber) FROM Protocol p1 (nolock)             
                          WHERE p1.ProtocolNumber = pro.ProtocolNumber            
                          AND p1.IsAmendment = 0 and p1.IsCurrent = 1)             
AND            
 agrfct.AgreementId = @agreementId            
GROUP BY             
pro.Title,            
pro.ShortTitle,            
pro.ProtocolNumber,            
PILastName,            
PIFirstName,            
status.[Name],            
pro.ExpirationDate,            
substatus.[Name],            
pro.SubStatusId      
,pro.ModuleType    
,pro.NumberOfAnimals      
,pro.Approved  
,pro.Enrolled          
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>          
-- G E T  Associated Agreements     R E S U L T S E T  F O R  U S E R            
-- Return a complete Associated Agreements information resultset from the agreementId          
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>          
 SELECT          
   award.[Description] AS 'Type'          
  ,CASE isnull(status.Name, 'R')          
            WHEN 'A' THEN 'Active'          
   WHEN 'I' THEN 'Inactive'          
   WHEN 'R' THEN 'Pending'          
   END AS 'Status'          
  ,agrfct.Title AS 'ProjectTitle'          
  ,agrfct.ProjectStartDate          
  ,agrfct.ProjectEndDate          
  ,agrfct.BudgetStartDate          
  ,agrfct.BudgetEndDate          
  ,famap.ActivationDate          
  ,CASE isnull(status.Name, 'R')          
   WHEN 'I' THEN famap.EffectiveDate          
   ELSE null          
   END AS 'InactivationDate'          
  ,agrfct.AgreementId          
  ,agrfct.FundNumber          
  ,agrfct.SponsorName          
  ,ISNULL(agrfct.PILastName,'') + ', ' + ISNULL(agrfct.PIFirstName,'') AS 'PIName'          
 FROM          
   dbo.GetAssociatedAgreements(@agreementId)assoagr          
 INNER JOIN          
  AgrAgreement_fct agrfct (nolock)          
 ON          
  agrfct.AgreementId = assoagr.AgreementId          
 INNER JOIN          
  AgrAgreement agreement (nolock)          
 ON          
  agrfct.AgreementId = agreement.[ID]          
 INNER JOIN          
  AgrFundAwardType award (nolock)          
 ON          
  award.[ID] = agreement.AgrAgreementTypeId          
 INNER JOIN          
  AgrFundStatusType status (nolock)          
 ON          
  status.[ID] = agrfct.FundStatusTypeId          
 INNER JOIN          
     AgrFund_AgrAgreement_Map famap (nolock)          
 ON          
     famap.AgrFundId = agrfct.FundId and           
     famap.AgrAgreementId = agrfct.AgreementId    

UNION

   SELECT          
   award.[Description] AS 'Type'          
  ,CASE isnull(status.Name, 'R')          
            WHEN 'A' THEN 'Active'          
   WHEN 'I' THEN 'Inactive'          
   WHEN 'R' THEN 'Pending'          
   END AS 'Status'          
  ,agrfct.Title AS 'ProjectTitle'          
  ,agrfct.ProjectStartDate          
  ,agrfct.ProjectEndDate          
  ,agrfct.BudgetStartDate          
  ,agrfct.BudgetEndDate          
  ,famap.ActivationDate          
  ,CASE isnull(status.Name, 'R')          
   WHEN 'I' THEN famap.EffectiveDate          
   ELSE null          
   END AS 'InactivationDate'          
  ,agrfct.AgreementId          
  ,agrfct.FundNumber          
  ,agrfct.SponsorName          
  ,ISNULL(agrfct.PILastName,'') + ', ' + ISNULL(agrfct.PIFirstName,'') AS 'PIName'          
 FROM          
  AgrAgreement agreement (nolock)        
  INNER JOIN        
 AgrAgreement_fct agrfct (nolock)      
      
 ON          
  agrfct.AgreementId = agreement.[ID]          
 INNER JOIN          
  AgrFundAwardType award (nolock)          
 ON          
  award.[ID] = agreement.AgrAgreementTypeId          
 INNER JOIN          
  AgrFundStatusType status (nolock)          
 ON          
  status.[ID] = agrfct.FundStatusTypeId          
 INNER JOIN          
     AgrFund_AgrAgreement_Map famap (nolock)          
 on       
     famap.AgrFundId = agrfct.FundId and           
     famap.AgrAgreementId = agrfct.AgreementId    
where 
agrfct.AgreementId =  @agreementId AND
agrfct.FundNumber <> @fundNumber  
      
          
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>          
-- G E T  Terms and Conditions    R E S U L T S E T  F O R  U S E R            
-- Return a complete Terms and Conditions resultset from the agreementId          
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>          
          
SELECT           
 ISNULL(terms.Terms, '') AS 'TermsAndConditions'          
FROM           
 AgrTermsAndConditions terms (nolock)          
WHERE           
 terms.AgrAgreementId = @agreementId          
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>          
-- G E T  MileStones    R E S U L T S E T  F O R  U S E R            
-- Return a complete MileStones resultset from the agreementId          
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>          
          
select distinct   
  ptDel.ReportName As 'Type'  
 ,cTab.Code_Desc As 'Description'  
 ,ptDel.CompletedDate as 'DateCompleted'  
 ,ptDel.DueDate As 'DateExpected' 
 ,case when IsNull(c.code_desc,'') like '%pending%' and ptDel.completed = 1 then 'Completed' 
		else IsNull(c.code_desc,'') end as 'Status'
from InfoedPTDeliverable ptDel
inner join InfoedCodeTab cTab (nolock)
On cTab.Inst_Code = ptDel.Inst_Code
and cTab.CodeId = ptDel.ReportCat
left outer join InfoEdPTDeliverableStatus s 
on s.DeliverableId = ptDel.DeliverableId
	and s.processedDate = (select max(s1.processedDate) 
							from InfoEdPTDeliverableStatus s1 
							where s1.DeliverableId = ptDel.DeliverableId)
left outer join InfoEdCodetab c on c.codeid = s.REPORTSTAT 
INNER JOIN AgrAgreement agr (nolock)  
On agr.PropNumber = ptDel.Prop_no
where agr.Id = @agreementId
order by ptDel.ReportName,ptDel.DueDate
--order by ptDel.CompletedDate Desc,ptDel.ReportName  


 if  @fundNumber is not null      
BEGIN      
 EXEC dbo.GetProjectHeader @fundNumber,NULL       
END  
SET NOCOUNT OFF          
END          
GO
